package sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import java.util.TreeSet;

import data.*;

public class SQLUtilityManagement {

	private static String _userMask = "INSERT INTO users(name, password, bdate, sex, type) VALUES(";
	private static String _moviesMask = "INSERT INTO movies(name, category, year, director, picture, rating, raters, language, length) VALUES(";
	private static String _ratingMask = "INSERT INTO ratings(user, movie, rating, date, description) VALUES(";

	public static void init() {
		// TODO Auto-generated method stub
	}

	public static UserTuple getUser(String user){
		System.out.println("checking if user exists");
		String cmd = "SELECT * FROM users WHERE name='"+user+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if(!r.first()){
				System.out.println("user does not exist");				
				return null;
			}else{
				if (r.getString("type").equals("admin")){
					return new AdminTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type"));
				}
				else {
					return new UserTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static MovieTuple getMovie(String movie){
		String cmd = "SELECT * FROM movies WHERE name='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if(!r.next()){
				System.out.println("BAD BAD BAD , MOVIE NOT FOUND IN DATABASE");
				return null;
			}else{
				return new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static TreeSet<RatingTuple> getRatingsByUser(String user){
		String cmd = "SELECT * FROM ratings WHERE user='"+user+"';";
		ResultSet r = executeQuery(cmd);
		TreeSet<RatingTuple> ans = new TreeSet<RatingTuple>(new CompartorMovie());
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(r.getString("movie"), user, r.getString("date"), r.getString("rating"), r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public static TreeSet<RatingTuple> getRatingsByMovie(String movie){
		String cmd = "SELECT * FROM ratings WHERE movie='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		TreeSet<RatingTuple> ans = new TreeSet<RatingTuple>(new CompartorUser());
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(movie,r.getString("user"), r.getString("date"), r.getString("rating"), r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public static RatingTuple getRating(String movie, String user){
		String cmd = "SELECT * FROM ratings WHERE user='"+user+"' AND movie='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if(!r.next()){
				System.out.println("BAD BAD BAD , RATING NOT FOUND IN DATABASE");
				//new Exception().printStackTrace();
				return null;
			}else{
				return RatingTuple.createNew(movie, user, r.getString("date"), r.getString("rating"), r.getString("description"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void updateUserDetails(String name, String bdate, Boolean sex){
		String cmd = "UPDATE users SET bdate='"+bdate+"' , sex='"+sex+"' WHERE name='"+name+"';";
		executeUpdate(cmd);
	}

	public static void updateUserPassword(String name, String password){
		String cmd = "UPDATE users SET password='"+password+"' WHERE name='"+name+"';";
		executeUpdate(cmd);
	}

	//	public static void setMovie(MovieTuple movie){
	//		String cmd = "UPDATE movies SET bdate='"+bdate+"' , sex='"+sex+"' WHERE name='"+movie.get_name()+"';";
	//		executeUpdate(cmd);
	//	}

	public static void addUser(UserTuple user){
		String cmd = _userMask + "'"+ user.get_name() +"', '"+
		user.get_password() + "', '" +
		user.get_bdate() + "', '" +
		user.is_sex()+"', '"+
		user.get_type() +"');";
		System.out.println("sql cmd: "+ cmd);
		executeUpdate(cmd);
	}

	public static void addMovie(MovieTuple movie){
		String cmd = _moviesMask + "'"+ movie.get_name() +"', '"+
		movie.get_category() + "', '" +
		movie.get_year() + "', '" +
		movie.get_director() +"', '"+
		movie.get_picture() + "', '" +
		movie.get_rating() +"', '"+
		movie.get_raters() + "', '" +
		movie.get_language() +"', '"+
		movie.get_length() +"');";
		executeUpdate(cmd);
	}

	public static void setRating(RatingTuple rec){
		String cmd = "UPDATE ratings SET rating='"+rec.get_rating()+"' ,"+
		" description='"+rec.get_description()+"' ,"+
		" date='"+rec.get_date()+"' " +
		" WHERE user='"+rec.get_user().get_name()+"' AND movie='"+rec.get_movie().get_name()+"';";
		executeUpdate(cmd);
	}
	public static void setRating(String user , String movie , String rating , String description , String date){
		String cmd = "UPDATE ratings SET rating='"+rating + "' ,"+
		" description='"+description+"' ,"+
		" date='"+date+"' " +
		" WHERE user='"+user+"' AND movie='"+movie+"';";
		executeUpdate(cmd);
	}

	public static void addRating(String user, String movie , String rating , String description,String date){
		System.out.println("description is: "+description);
		String cmd = _ratingMask + "'"+ user +"', '"+
		movie + "', '" +
		rating + "', '" +
		date + "', '" +
		description +"');";
		executeUpdate(cmd);

	}

	public static void addRating(RatingTuple rec){
		String cmd = _ratingMask + "'"+ rec.get_user().get_name() +"', '"+
		rec.get_movie().get_name() + "', '" +
		rec.get_rating() + "', '" +
		rec.get_date() + "', '" +
		rec.get_description() +"');";
		executeUpdate(cmd);

	}

	public static void updateMovieRating(String movie, String newRate) {
		String cmd = "UPDATE movies SET rating='"+newRate+"' " +
		" WHERE name='"+movie+"' ;";
		executeUpdate(cmd);
	}

	public static void updateMovieRating(String movie, String newRate, String raters) {
		String cmd = "UPDATE movies SET rating='"+newRate+"' " +
		", raters='"+raters +"' " +
		" WHERE name='"+movie+"' ;";
		executeUpdate(cmd);
	}




	public static Set<MovieTuple> getAllMovies() {
		String cmd = "SELECT * FROM movies;";
		ResultSet r = executeQuery(cmd);
		HashSet<MovieTuple> ans = new HashSet<MovieTuple>();
		try {
			while(r.next()){
				ans.add(new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Set<UserTuple> getAllUsers() {
		String cmd = "SELECT * FROM users;";
		ResultSet r = executeQuery(cmd);
		HashSet<UserTuple> ans = new HashSet<UserTuple>();
		try {
			while(r.next()){
				if (r.getString("type").equals("admin")){
					ans.add(new AdminTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type")));
				} else {
					ans.add(new UserTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type")));
				}
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Set<RatingTuple> getAllRating() {
		String cmd = "SELECT * FROM ratings;";
		ResultSet r = executeQuery(cmd);
		HashSet<RatingTuple> ans = new HashSet<RatingTuple>();
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(r.getString("movie"),r.getString("user"),r.getString("date"),r.getString("rating"),r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}


	public static String[] getMovieNames() {
		String cmd = "SELECT name FROM movies;";
		ResultSet r = executeQuery(cmd);
		HashSet<String> ans = new HashSet<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String[] getUserNames(String name) {
		String cmd = "SELECT name FROM users WHERE name<>'"+name+"';";
		ResultSet r = executeQuery(cmd);
		HashSet<String> ans = new HashSet<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void deleteMovie(String movie){
		String cmd = "DELETE FROM movies WHERE name='"+movie+"';";
		executeUpdate(cmd);
	}
	public static void deleteUser(String user){
		String cmd = "DELETE FROM users WHERE name='"+user+"';";
		executeUpdate(cmd);
	}
	public static void deleteRating(String user, String movie){
		String cmd = "DELETE FROM ratings WHERE user='"+user+"' AND movie='"+movie+"';";
		executeUpdate(cmd);
	}


	private static void executeUpdate(String cmd){
		System.out.println(cmd);
		Statement stmt;
		String url = "jdbc:mysql://localhost:3306/movies_db";
		//String url = "jdbc:mysql://132.72.171.140:3306/movies_db";
		//Get a connection to the database for a
		// user named root with a blank password.
		// This user is the default administrator
		// having full privileges to do anything.
		Connection con;
		try {
			con = DriverManager.getConnection(url,"admin", "dog");
			stmt = con.createStatement();
			stmt.executeUpdate(cmd);
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	private static ResultSet executeQuery(String cmd){
		System.out.println(cmd);
		Statement stmt;
		String url = "jdbc:mysql://localhost:3306/movies_db";
	//	String url = "jdbc:mysql://132.72.171.140:3306/movies_db";
		//Get a connection to the database for a
		// user named root with a blank password.
		// This user is the default administrator
		// having full privileges to do anything.
		Connection con;
		try {
			con = DriverManager.getConnection(url,"admin", "dog");
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			ResultSet ans = stmt.executeQuery(cmd);
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static double getAvg(String user) {
		String cmd = "SELECT AVG(rating) FROM ratings WHERE user='"+user+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if (r.next())
				return r.getDouble("AVG(rating)");
				//System.out.println("look here "+r.getDouble("AVG(rating)"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}


	//TODO there are many more optimizations we can use using the SQL engine (especially with the algorithm) , by moving more operations to the SystemData interface

}
